1. Field of the Invention
The subject disclosure relates to managing the structure of database objects in database servers, and more particularly, to an improved system and method for updating database objects across a plurality of database servers in a network.
2. Background of the Related Art
The use of computers for storing and accessing information has become ubiquitous in the modern workplace. Virtually every industry that uses computers stores some or all of the necessary information in databases for easy access. Typically, the data is organized into tables as relational databases. A database management system (“DBMS”) controls user access to the data and manages version control and updating so that multiple users can access the data therein.
The data tables are frequently stored in accordance with Structured Query language (“SQL”) specifications. SQL is the language of choice for most modern multi-user, relational databases, i.e., SQL provides the syntax and idioms (“language”) needed to talk to (“query”) relational databases in a standardized, cross-platform/product way (“structured”). SQL uses tables typically composed of rows (records) and each row is composed of columns (fields). SQL refers to data as a set of pointers and provides predefined procedures to allow use of any value in a table to relate other tables in a database. SQL databases are indexed by a “data dictionary”. The structure of database objects is represented by a collection of database schema files. The schema files result from data modeling. The schema files contain the standard Data Definition Language (“DDL”) commands that are run against a database server to create the database objects. To access data in the tables, SQL navigates the system and produces “views” based on search criteria defined in an SQL query. A view is essentially a dynamically generated result table that is assembled based upon the parameters defined in a query.
SQL commands fall into two categories, DDL and a data manipulation language (“DML”). The DDL is used to define the database with commands such as create, alter and drop. The DML is used to manage the database with commands such as insert, update and delete. SQL shells are common and typically come bundled with every database. The SQL shells are usually simple filters which allow logging onto the database, execute commands and receive output. The SQL shells range from simple to complex. SQL shells provide access to the database. Socket-based middleware performs a similar function to SQL shells, but over a network. Thus, with a socket-based system, a user can send commands to a database in Connecticut while working in New York City by using TCP/IP over the Internet to connect. Of course, whether a command shell or a socket based connection is used, there is a need to know the language of the database in order to send commands. Generally, a sequence of statements is required to perform anything but the simplest of operations.
SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. When a user submits a query to an SQL database using SQL, the database will consult the data dictionary and access the tables that the user has requested data from. A view is compiled based upon the criteria defined in the SQL query. Similarly, a view could be composed of the results of a query on several tables all at once (sometimes called a “join”). However, the standard SQL commands such as select, insert, update, delete, create, and drop can be used to accomplish most data management tasks. SQL alleviates the need to perform the details of data storage and retrieval and the internal workings of the database and how it stores data on the file system need not be known.
However, this ease of working with the data has presented a challenge. Medium to large size companies can have a significant number of information technology specialists. Each specialist may be assigned a single task, yet each and every action related to the databases must be tracked and publicized. In addition, companies generate sufficient amounts of data such that the organization of data becomes very complex. Efficiency requires an infallible level of reliability in the storage and access of the data.
To facilitate the management of databases, version control systems have been developed such as the concurrent versions system (“CVS”). CVS records the history of source files and allows retrieval of old versions for debugging purposes. CVS stores the old versions by keeping track of the differences between versions. Despite tools such as CVS, the modification and update of the databases can occur without proper permission. As databases are modified and updated, control of the database configuration is lost. In such circumstances, standards are not maintained in every data warehousing facility and uniformity throughout a system can be lost. Further, certain users who need to apprised of modifications are not notified and problems proliferate. Still further, the database objects may not match the schema files and thus, the query may not be successfully completed.
There is a need, therefore, for an improved method for managing a plurality of database servers that permits control of the release of updates by verifying permissions and maintaining standards. There is also a need to supply a test mode to verify performance prior to production deployment. In addition, there is also a need to produce documentation that corresponds to modifications in the databases in order to track the database configuration.